Database configuration and reconfiguration operations can have a significant effect on the availability of user applications that need access to databases undergoing structural changes. The Tandem.TM. NonStop.TM. SQL/MP relational database management system (DBMS), prior to the present invention, allowed read access, but not write access, to the portions of the database table undergoing the restructuring operation.
Although most users perform these operations infrequently, their duration can account for thousands of minutes of application outages per year. A discussion of the cost of application outages appears in the article "An Overview of NonStop SQL/MP," Ho et al., Tandem Systems Review, Jul. 1994.
The present invention eliminates most of the downtime associated with four database reconfiguration operations: Move Partition, Split Partition, Move Partition Boundary, and Create Index. The Move Partition procedure moves a partition that resides on one disk to another disk. The Split Partition procedure splits one partition into two. The Move Partition Boundary procedure moves rows of a base table between adjacent partitions, typically shifting rows from a large partition to a less large partition. The Create Index procedure creates an efficient alternate access path to a database table by ordering data according to the value specified in the key columns. The Create Unique Index variant of this procedure ensures that there is exactly one alternate access path to each record in the database table.
The implementation of these procedures in the present invention substantially reduces, but does not eliminate, associated outages. Even with the present invention, user database activity continues to be restricted for about one minute or less per database restructuring operation. The outage time varies depending on the number of user transactions running against the table being restructured, the size of those transactions, and the number of partitions in the affected table.
It is therefore a primary object of the present invention to enable substantially improved user transaction access to a database table while the table is undergoing a structural change operation.
Another object of the present invention is to enable Move Partition, Split Partition, Move Partition Boundary, and Create Index operations to be performed on a database table while enabling user transactions to continue to be performed on the table, except during a short final phase of those operations.
Another object of the present invention is to avoid the use of "side files" for the above mentioned database reconfiguration operations, so as to reduce the overhead disk space requirements associated with these operations to almost zero.
Another object of the present invention is to implement the above mentioned database reconfiguration operations using procedures that directly read from the transaction audit log, thereby making these database reconfiguration operations similar to a database recovery process.
Another object of the present invention is to implement the database reconfiguration operations using well established, optimized, database recovery process procedures thereby making the database reconfiguration operations efficient in terms of computation time and in terms of the computer resources used.
Yet another object of the present invention is to utilize substantially similar database reconfiguration procedures for four distinct database reconfiguration operations, thereby improving the reliability of those procedures and simplifying maintenance of and updates to those procedures.